#!/usr/bin/env python3
# -*- coding:utf-8 -*-
## author : cypro666
## date   : 2014.6.28
"""
A simple mysql-database connection wrapper for operation, thread-safe.
I really hate MySQL and other DBMS...
"""
import sys, time
from threading import Lock
from pymysql import connections
from pymysql import err as mysqlerr
from pymysql.cursors import SSCursor
from getpass import getpass

from .debug import time_meter, print_exception
from .utilities import loadjson

__all__ = ['OpMysql']


class OpMysql(object):
    """Mysql server connector"""
    
    def __init__(self, **kwargs):
        """Initialize and connect to mysqld, user should input login info"""
        self.__con = None
        self.__lock = Lock()
        try:
            user = kwargs['username']
            passwd = kwargs['password']
            host, port, dbname= kwargs['host'], kwargs['port'], kwargs['database']
            sys.stdout.write('Try to connect '+user+'@'+host+':'+port+' '+dbname+'\n')
            self.__con = connections.Connection(host = str(host), port = int(port), 
                                                user = str(user), passwd = str(passwd), 
                                                database = str(dbname), charset = 'utf8')
        except Exception as e:
            raise RuntimeError('OpMysql: Failed!')
        self.__cur = self.__con.cursor()
    
    
    def __del__(self):
        if self.__con:
            self.__con.commit()
            self.__cur.close()
            self.__con.close()
        sys.stdout.write('OpMysql: connection closed!\n')
    
    def select_database(self, dbname):
        with self.__lock:
            self.__con.select_db(dbname)
    
    
    def execute(self, sql, args = None):
        """execute a sql with commit"""
        with self.__lock:
            try:
                cur = self.__con.cursor()
                cur.execute(sql, args = args)
                self.__con.commit()  # first, commit
                cur.close()          # then, close
            except mysqlerr.Error:
                print_exception('OpMysql.query')
            except Exception as e:
                raise e
            
    def execute_nocommit(self, sql, args = None):
        """execute a sql without commit, so user should commit() later"""
        with self.__lock:
            try:
                self.__cur.execute(sql, args = args)
            except mysqlerr.Error:
                print_exception('OpMysql.query')
            except Exception as e:
                raise e
            
    
    def commit(self):
        with self.__lock:
            self.__con.commit()
    
    
    def fetch(self, sql, args = None, size = 0):
        """fetch(select) data from db and return iterable"""
        with self.__lock:
            cur = self.__con.cursor()
            cur.execute(sql, args = args)
            try:
                if size <= 0 : 
                    items = cur.fetchall()
                else:
                    items = cur.fetchmany(size)
                cur.close()
                return items
            except Exception as e:
                cur.close()
                return None

    
    def query(self, sql, buffered = True):
        """only for simple query"""
        with self.__lock:
            try:
                return self.__con.query(sql, unbuffered = not buffered)
            except Exception:
                print_exception('OpMysql.query')



@time_meter(__name__)
def test():
    dbf = OpMysql(username='root', password=getpass(),
                  host='localhost', port='3306', database='mysql')
    dbf.select_database('mysql')
    
    print(dbf.query("""SELECT * FROM `user` LIMIT %d;""" % (10,), True))

    sql = """SELECT * FROM `user` WHERE host=%s;"""
    cur = dbf.fetch(sql, ("localhost",))
    for i in cur:
        print(i)
    
    result = dbf.fetch(sql = """SELECT COUNT(*) FROM `user`""", args = None, size = -1)
    print(result)












